package com.o2o.repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.context.annotation.Scope;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.mysql.jdbc.Statement;
import com.o2o.base.BaseRepository;
import com.o2o.exception.CategoryNumberErrorException;
import com.o2o.model.topic.Category;
import com.o2o.model.topic.Product;
import com.o2o.model.topic.Topic;
import com.o2o.model.topic.TopicContent;
import com.o2o.model.user.User;
import com.o2o.util.JsoupUtil;

@Scope("prototype") 
@Repository
public class TopicRepository  extends BaseRepository{
	
	public List<Category> getAllCategoryTree(int status){
		String sql = "select * from v_category where status=?";
		return simpleJdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(Category.class), new Object[]{status});
	}
	/**
	 * 根据categoryNumber获取该number的一级分类
	 * @param categoryNumber 该分类的number
	 * @return 一级分类Category
	 * @throws CategoryNumberErrorException 分类number错误
	 */
	public Category getFirstCategoryByNumber(int categoryNumber,int status) throws CategoryNumberErrorException {
		int first = categoryNumber / 1000000;
		if(first > 0 && first <10){
			int number =  categoryNumber/1000000*1000000;
			String sql = "select * from v_category where status=? and category_number=?";
			return simpleJdbcTemplate.queryForObject(sql,BeanPropertyRowMapper.newInstance(Category.class), new Object[]{1,number});
		}else throw new CategoryNumberErrorException("CategoryNumber Error,Frist category number must > 0; x,xx,xx,xx");
	}
	/**
	 * 根据categoryNumber获取该number的二级分类
	 * @param categoryNumber 该分类的number
	 * @return 二级分类Category
	 * @throws CategoryNumberErrorException 分类number错误
	 */
	public Category getSecondCategoryByNumber(int categoryNumber,int status) throws CategoryNumberErrorException {
		int first, second;
		first = categoryNumber / 1000000;
		second = (categoryNumber % 1000000) / 10000;
		if (first > 0 && second > 0) {// 该categoryNumber含有二级分类
			int number = categoryNumber / 10000 * 10000;
			String sql = "select * from v_category where status=? and category_number=?";
			return simpleJdbcTemplate.queryForObject(sql,BeanPropertyRowMapper.newInstance(Category.class),new Object[] { 1, number });
		} else throw new CategoryNumberErrorException("CategoryNumber Error,Second category number must >0;x,xx,xx,xx");
	}
	/**
	 * 根据categoryNumber获取该number的三级分类
	 * @param categoryNumber 该分类的number
	 * @return 三级分类Category
	 * @throws CategoryNumberErrorException 分类number错误
	 */
	public Category getThirdCategoryByNumber(int categoryNumber,int status) throws CategoryNumberErrorException {
		int first, second, third;
		first = categoryNumber / 1000000;
		second = (categoryNumber % 1000000) / 10000;
		third = (categoryNumber % 10000) / 100;
		if (first > 0 && second > 0 && third > 0) {// 该分类有三级分类
			int number = categoryNumber / 100 * 100;
			String sql = "select * from v_category where status=? and category_number=?";
			return simpleJdbcTemplate.queryForObject(sql,BeanPropertyRowMapper.newInstance(Category.class),new Object[] { 1, number });
		} else throw new CategoryNumberErrorException("CategoryNumber Error,Third category number must >0;x,xx,xx,xx");
	}
	
	/**
	 * 根据categoryNumber获取该number的四级分类
	 * @param categoryNumber 该分类的number
	 * @return 四级分类Category
	 * @throws CategoryNumberErrorException 分类number错误
	 */
	public Category getFourthCategoryByNumber(int categoryNumber,int status) throws CategoryNumberErrorException {
		int first, second, third, fourth;
		first = categoryNumber / 1000000;
		second = (categoryNumber % 1000000) / 10000;
		third = (categoryNumber % 10000) / 100;
		fourth = categoryNumber % 100;
		if (first > 0 && second > 0 && third > 0 && fourth > 0) {// 该分类有四级分类
			String sql = "select * from v_category where status=? and category_number=?";
			return simpleJdbcTemplate.queryForObject(sql,BeanPropertyRowMapper.newInstance(Category.class),new Object[] { 1, categoryNumber });
		} else throw new CategoryNumberErrorException("CategoryNumber Error,Fourth category number must >0;x,xx,xx,xx");
	}
	/**
	 * 根据父分类id找到所有的子分类
	 * @param pid 父类分类id
	 * @return List<Category>所有的子分类
	 */
	public List<Category> getSubCategorysByPid(long pid,int status){
		String sql = "select * from v_category where pid=? and status=?";
		return simpleJdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(Category.class), new Object[]{pid,1});
	}
	
	/**
	 * 根据分类number找到所有的子分类
	 * @param categoryNumber 分类number
	 * @return List<Category>所有的子分类
	 * @throws CategoryNumberErrorException 分类number错误
	 */
	public List<Category> getSubCategorysByCategoryNumber(int categoryNumber,int status) throws CategoryNumberErrorException {
		int level = 0, first, second, third, fourth;
		String dependLike = "";// 需要like的值
		first = categoryNumber / 1000000;
		second = (categoryNumber % 1000000) / 10000;
		third = (categoryNumber % 10000) / 100;
		fourth = categoryNumber % 100;
		if (categoryNumber == 0) {// 查询所有一级分类
			level = 1;
			dependLike = "%000000";
		}
		if (first > 0 && second == 0 && third == 0 && fourth == 0) {// 查询所有二级分类
			level = 2;
			dependLike = first + "%0000";
		}
		if (first > 0 && second > 0 && third == 0 && fourth == 0) {// 查询所有三级分类
			level = 3;
			dependLike = "" + first + second + "%00";
		}
		if (first > 0 && second > 0 && third > 0 && fourth == 0) {// 查询所有四级分类
			level = 4;
			dependLike = categoryNumber / 100 + "%";
		}
		if(level > 0){
			String sql = "SELECT * FROM v_category WHERE category_number LIKE ? AND LEVEL=? and status=?;";
			return simpleJdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(Category.class),new Object[] { dependLike, level, 1 });
		}else throw new CategoryNumberErrorException("CategoryNumber Error,x,xx,xx,xx");
	}
	/**
	 * 保存一个主题帖
	 * @param uid 用户id
	 * @param userName 用户名称
	 * @param topicTitle 主题帖title
	 * @param time 保存时间
	 * @param topicString 主题帖的楼主说
	 * @param productId 商品id
	 * @param categoryNumber 主题帖的分类number
	 * @param productType 商品分类
	 * @param topicPrice 主题帖的价格，不同于商品价格，这个价格一般属于活动价
	 * @return 序列化后的id,若保存失败，则返回0
	 */
	/*@Transactional*/
	public long saveTopic(final long uid,final String userName,final String topicTitle,final String topicString,final long productId,final int categoryNumber,final int productType,final float topicPrice,final long time){
		final String sql = "insert into v_topic(topic_title,topic_string,topic_create_time,uid,topic_user_name,topic_status,product_id,category_number,product_type,topic_price) values(?,?,?,?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int update = jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
				ps.setString(1, topicTitle);
				ps.setString(2, topicString);
				ps.setLong(3, time/1000);
				ps.setLong(4, uid);
				ps.setString(5, userName);
				ps.setInt(6, 1);
				ps.setLong(7, productId);
				ps.setInt(8, Integer.parseInt(""+productType+categoryNumber));
				ps.setInt(9, productType);
				ps.setFloat(10, topicPrice);
				return ps;
			}
		}, keyHolder);
		if(update > 0){
			long generatedId = keyHolder.getKey().longValue();
			return generatedId;
		}else return 0;
	}
	/**
	 * 保存一个主题帖的商品
	 * @param productName 商品名称
	 * @param productMarketPrice 商品市场价
	 * @param productCategoryNumber 商品分类number
	 * @param productPicturePath 商品主图的路径
	 * @param productBrand 商品品牌
	 * @param productType 商品的类型，1为标准品，2为本地生活服务，3为二手商品
	 * @param productUrl 商品的网上链接
	 * @param productDescription 商品描述
	 * @param uid 用户id
	 * @param time 保存时间
	 * @return 序列化后的id,若保存失败，则返回0
	 */
	public long saveProduct(final String productName,final float productMarketPrice,final int productCategoryNumber,final String productPicture,final String productBrand,final int productType,final String productUrl,final String productDescription,final long uid,final long time){
		final String sql = "insert into v_product(product_name,product_market_price,product_description,product_create_time,product_url,product_status,product_type,product_category_number,product_brand,product_main_picture,uid) values(?,?,?,?,?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int update =  jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
				ps.setString(1, productName);
				ps.setFloat(2, productMarketPrice);
				ps.setString(3, productDescription);
				ps.setLong(4, time/1000);
				ps.setString(5, productUrl);
				ps.setInt(6, 1);
				ps.setInt(7, productType);
				ps.setInt(8, productCategoryNumber);
				ps.setString(9, productBrand);
				ps.setString(10, productPicture);
				ps.setLong(11, uid);
				return ps;
			}
		}, keyHolder);
		if(update > 0){
			long generatedId = keyHolder.getKey().longValue();
			return generatedId;
		}else return 0;
	}
	
	/**
	 * 保存主题帖讨论
	 * @param topicId 主题帖id
	 * @param userId 发表的用户id
	 * @param content 讨论内容
	 * @param fatherId 回复评论的评论id，自关联，为0的话，是主题帖的讨论
	 * @return 序列化后的id,若保存失败，则返回0
	 */
	public long saveTopicContent(final long topicId,final long fromUserId,final long toUserId,final String content,final long fatherId,final Long createTime){
		final String sql = "insert into v_topic_content(topic_id,content,from_user_id,to_user_id,great_times,create_time,father_id) values(?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int update =  jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
				ps.setLong(1, topicId);
				ps.setString(2, content);
				ps.setLong(3, fromUserId);
				ps.setLong(4, toUserId);
				ps.setInt(5, 0);
				ps.setLong(6, createTime/1000);
				ps.setLong(7, fatherId);
				return ps;
			}
		}, keyHolder);
		if(update > 0){
			long generatedId = keyHolder.getKey().longValue();
			return generatedId;
		}else return 0;
	}
	/**
	 * 保存用户评论主题帖通知信息
	 * @param content 通知以及评论内容
	 * @param createTime 创建时间
	 * @param fromUserId 来自用户评论的user id
	 * @param toUserId 评论给用户的id
	 * @param topicId 评论的主题帖id
	 * @param contentId 评论的持久化id
	 * @param type 通知类型，1为评论主题帖，2为赞主题帖，3为回复评论，4为赞评论
	 * @return 持久化后的主键id，若为0则插入不成功
	 */
	public long saveTopicNotice(final long topicId,final String content,final long createTime,final long fromUserId,final long toUserId,final long contentId,final int type){
		final String sql = "insert into v_topic_notice(to_user_id,from_user_id,topic_id,content,message_is_read,create_time,content_id) values(?,?,?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		int update =  jdbcTemplate.update(new PreparedStatementCreator() {
			@Override
			public PreparedStatement createPreparedStatement(Connection connection)
					throws SQLException {
				PreparedStatement ps = connection.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS); 
				ps.setLong(1, toUserId);
				ps.setLong(2, fromUserId);
				ps.setLong(3, topicId);
				ps.setString(4, content);
				ps.setInt(5,type );
				ps.setLong(6, createTime/1000);
				ps.setLong(7, contentId);
				return ps;
			}
		}, keyHolder);
		if(update > 0){
			long generatedId = keyHolder.getKey().longValue();
			return generatedId;
		}else return 0;
	}
	/**
	 * 根据主题帖id获取该主题帖详细，带product信息
	 * @param topicId 主题帖id
	 * @return Product(带Topic信息)
	 */
	public Topic getTopicProductByTopicId(long topicId) {
		//sql2使用子连接+left join查询
//		String sql2 = "SELECT tmp.*,product.id AS pid,product.product_name,product.product_market_price,product.product_description,product.product_url,product.product_status,product.product_type,product.product_category_number,product.product_brand,product.product_main_picture,product.uid,product.product_create_time FROM (SELECT topic.id AS tid,topic.topic_title,topic.topic_string,topic.uid,topic.topic_status,topic.topic_browse_count,topic.topic_content_count,topic.topic_user_count,topic.product_id,topic.topic_user_name,topic.topic_create_time FROM v_topic topic USE INDEX(id_pid) WHERE  topic.id=2430234 AND topic.topic_status=1 ) AS tmp LEFT JOIN v_product product ON tmp.product_id=product.id ";
		String sql = "SELECT topic.id AS tid,topic.topic_great_count,topic.topic_hot,topic.category_number,topic.topic_title,topic.topic_string,topic.uid,topic.topic_status,topic.topic_browse_count,topic.topic_content_count,topic.topic_user_count," +
				"topic.product_id,topic.topic_user_name,topic.topic_create_time,product.id AS pid,product.product_name," +
				"product.product_market_price,product.product_description,product.product_url,product.product_status,product.product_type,product.product_category_number,product.product_brand,product.product_main_picture,product.uid,product.product_create_time " +
				"FROM v_topic topic LEFT JOIN v_product product ON topic.product_id=product.id WHERE topic.topic_status=? and topic.id=? ";
		return simpleJdbcTemplate.queryForObject(sql,new RowMapper<Topic>() {
			@Override
			public Topic mapRow(ResultSet rs, int row) throws SQLException {
				Topic t = new Topic();  
                try {  
                	t.setId(rs.getLong("tid"));  
                	t.setTopicTitle(rs.getString("topic_title"));
                	t.setTopicString(rs.getString("topic_string"));
                	t.setUid(rs.getLong("uid"));
                	t.setTopicStatus(rs.getInt("topic_status"));
                	t.setTopicHot(rs.getInt("topic_hot"));
                	t.setCategoryNumber(rs.getInt("category_number"));
                	t.setTopicBrowseCount(rs.getInt("topic_browse_count"));
                	t.setTopicContentCount(rs.getInt("topic_content_count"));
                	t.setTopicGreatCount(rs.getInt("topic_great_count"));
                	
                	t.setTopicUserCount(rs.getInt("topic_user_count"));
                	t.setProductId(rs.getLong("product_id"));
                	t.setTopicUserName(rs.getString("topic_user_name"));
                	t.setTopicCreateTime(rs.getLong("topic_create_time"));
                	
                	Product p = new Product();
                	p.setId(rs.getLong("pid"));
                	p.setProductName(rs.getString("product_name"));
                	p.setProductMarketPrice(rs.getFloat("product_market_price"));
                	p.setProductDescription(rs.getString("product_description"));
                	p.setProductUrl(rs.getString("product_url"));
                	p.setProductStatus(rs.getInt("product_status"));
                	p.setProductType(rs.getInt("product_type"));
                	p.setProductCategoryNumber(rs.getInt("product_category_number"));
                	p.setProductBrand(rs.getString("product_brand"));
                	p.setProductMainPicture(rs.getString("product_main_picture"));
                	p.setUid(rs.getLong("uid"));
                	p.setProductCreateTime(rs.getLong("product_create_time"));
                	t.setProduct(p);
                } catch (SQLException e) {  
                }  
				return t;
			}
		}, new Object[]{1,topicId});
	}
	
	/**
	 * 根据主题帖id获取该主题帖详细，带product信息
	 * @param topicId 主题帖id
	 * @return Product(带Topic信息)
	 */
	public List<Topic> getTopicProductListByTopicIds(final String[] ids) throws Exception{
		//sql2使用子连接+left join查询
//		String sql2 = "SELECT tmp.*,product.id AS pid,product.product_name,product.product_market_price,product.product_description,product.product_url,product.product_status,product.product_type,product.product_category_number,product.product_brand,product.product_main_picture,product.uid,product.product_create_time FROM (SELECT topic.id AS tid,topic.topic_title,topic.topic_string,topic.uid,topic.topic_status,topic.topic_browse_count,topic.topic_content_count,topic.topic_user_count,topic.product_id,topic.topic_user_name,topic.topic_create_time FROM v_topic topic USE INDEX(id_pid) WHERE  topic.id=2430234 AND topic.topic_status=1 ) AS tmp LEFT JOIN v_product product ON tmp.product_id=product.id ";
		StringBuffer buff = new StringBuffer();
		String param = "";
		for (int i=ids.length-1;i>=0;i--) {
			if(ids[i] != null && !"".equals(ids[i]))
				buff.append(ids[i]+",");
		}
		if(buff.length() > 1){
			param = buff.substring(0,buff.length()-1);
		}
		String sql = "SELECT topic.id AS tid,topic.topic_great_count,topic.topic_title,topic.topic_string,topic.uid,topic.topic_status,topic.topic_browse_count,topic.topic_content_count,topic.topic_user_count," +
				"topic.product_id,topic.topic_hot,topic.category_number,topic.topic_user_name,topic.topic_create_time,product.id AS pid,product.product_name," +
				"product.product_market_price,product.product_description,product.product_url,product.product_status,product.product_type,product.product_category_number,product.product_brand,product.product_main_picture,product.uid,product.product_create_time " +
				"FROM v_topic topic LEFT JOIN v_product product ON topic.product_id=product.id WHERE topic.topic_status=? and topic.id in ("+param.toString()+") ";
		return jdbcTemplate.query(sql, new RowMapper<Topic>() {
			@Override
			public Topic mapRow(ResultSet rs, int row) throws SQLException {
				Topic t = new Topic();  
                try {  
                	t.setId(rs.getLong("tid"));  
                	t.setTopicTitle(rs.getString("topic_title"));
                	t.setTopicString(rs.getString("topic_string"));
                	t.setUid(rs.getLong("uid"));
                	t.setTopicStatus(rs.getInt("topic_status"));
                	t.setTopicHot(rs.getInt("topic_hot"));
                	t.setCategoryNumber(rs.getInt("category_number"));
                	t.setTopicBrowseCount(rs.getInt("topic_browse_count"));
                	t.setTopicContentCount(rs.getInt("topic_content_count"));
                	t.setTopicUserCount(rs.getInt("topic_user_count"));
                	t.setTopicGreatCount(rs.getInt("topic_great_count"));
                	t.setProductId(rs.getLong("product_id"));
                	t.setTopicUserName(rs.getString("topic_user_name"));
                	t.setTopicCreateTime(rs.getLong("topic_create_time")*1000);
                	Product p = new Product();
                	p.setId(rs.getLong("pid"));
                	p.setProductName(rs.getString("product_name"));
                	p.setProductMarketPrice(rs.getFloat("product_market_price"));
                	p.setProductDescription(rs.getString("product_description"));
                	p.setProductUrl(rs.getString("product_url"));
                	p.setProductStatus(rs.getInt("product_status"));
                	p.setProductType(rs.getInt("product_type"));
                	p.setProductCategoryNumber(rs.getInt("product_category_number"));
                	p.setProductBrand(rs.getString("product_brand"));
                	p.setProductMainPicture(rs.getString("product_main_picture"));
                	p.setUid(rs.getLong("uid"));
                	p.setProductCreateTime(rs.getLong("product_create_time")*1000);
                	t.setProduct(p);
                } catch (SQLException e) {  
                }  
				return t;
			}
		}, new Object[]{1});
	}
	/**
	 * 根据评论id获取评论的详细信息
	 * @param contentId 评论id 
	 * @return TopicContent
	 */
	public TopicContent getTopicContentById(long contentId){
		String subSql = "SELECT user_t.name AS to_user_name,user_t.id AS to_user_id,user_t.user_picture AS to_user_picture," +
				"user_f.name AS from_user_name,user_f.id AS from_user_id,user_f.user_picture AS from_user_picture,tmp.* " +
				"FROM v_user user_t FORCE INDEX(PRIMARY) " +
				"RIGHT JOIN (SELECT content.id,content.content,content.topic_id,content.create_time ,content.father_id,content.from_user_id,content.to_user_id  " +
				"FROM v_topic_content content  WHERE content.id=?) AS tmp " +
						"ON tmp.to_user_id=user_t.id LEFT JOIN v_user USER_f FORCE INDEX(PRIMARY) ON tmp.from_user_id=user_f.id  ";
		//获取所有的评论的回复
		TopicContent content = jdbcTemplate.queryForObject(subSql, new RowMapper<TopicContent>(){  
			@Override
			public TopicContent mapRow(java.sql.ResultSet rs, int arg1)
					throws SQLException {
				TopicContent content = new TopicContent();  
                try {  
                	content.setId(rs.getLong("id"));  
                	content.setContent(rs.getString("content"));
                	content.setTopicId(rs.getLong("topic_id"));
                	content.setCreateTime(rs.getInt("create_time"));
                	content.setFatherId(rs.getLong("father_id"));
                	User fromUser = new User();
                	
                	fromUser.setName(rs.getString("from_user_name"));
                	fromUser.setId(rs.getLong("from_user_id"));
                	fromUser.setUserPicture(rs.getString("from_user_picture"));
                	content.setFromUser(fromUser);
                	
                	User toUser = new User();
                	toUser.setName(rs.getString("to_user_name"));
                	toUser.setId(rs.getLong("to_user_id"));
                	toUser.setUserPicture(rs.getString("to_user_picture"));
                	content.setToUser(toUser);
                } catch (SQLException e) {  
                }  
                return content;
			}  
        },new Object[]{contentId});
		return content;
	}
	
	/**
	 * 根据主题帖topicId获取所有的主题帖讨论,得到的size=pageSize+1
	 * @param topicId 主题帖id
	 * @param page 当前页
	 * @param pageSize 分页大小
	 * @return List<TopicContent>
	 */
	public List<TopicContent> getTopicContentByTopicId(long topicId,int page,int pageSize){
		List<TopicContent> result = new ArrayList<TopicContent>();
		String sql = "SELECT user.name AS from_user_name,user.id AS from_user_id,user.user_picture AS from_user_picture,tmp.* " +
				"FROM (SELECT content.id,content.content,content.topic_id,content.create_time ,content.father_id,content.from_user_id,content.great_times FROM v_topic_content content USE INDEX(i_time) " +
				"WHERE  content.father_id=0 and content.topic_id=? ORDER BY content.create_time DESC LIMIT ?,?) AS tmp LEFT JOIN v_user USER FORCE INDEX(PRIMARY) ON tmp.from_user_id=user.id ";
		List<TopicContent> contents = jdbcTemplate.query(sql, new RowMapper<TopicContent>(){  
			@Override
			public TopicContent mapRow(java.sql.ResultSet rs, int arg1)
					throws SQLException {
				TopicContent content = new TopicContent();  
                try {  
                	content.setId(rs.getLong("id"));  
                	content.setContent(JsoupUtil.unParseHtml(rs.getString("content")));
                	content.setTopicId(rs.getLong("topic_id"));
                	content.setCreateTime(rs.getInt("create_time"));
                	content.setFatherId(rs.getLong("father_id"));
                	content.setGreateTimes(rs.getInt("great_times"));
                	User fromUser = new User();
                	
                	fromUser.setName(rs.getString("from_user_name"));
                	fromUser.setId(rs.getLong("from_user_id"));
                	fromUser.setUserPicture(rs.getString("from_user_picture"));
                	content.setFromUser(fromUser);
                	
                } catch (SQLException e) {  
                }  
                return content;
			}  
        }, new Object[]{topicId,(page * pageSize - pageSize), pageSize+1});//
		if(contents != null && !contents.isEmpty()){
			//获取评论的回复list
			StringBuffer sb = new StringBuffer();
			String fatherIds = "";//所有fatherid，即所有的主题帖的评论
			Map<Long,TopicContent> contentMap = new HashMap<Long,TopicContent>();//改map中存放所有的主题帖评论,key为评论id
			for (TopicContent tc : contents) {
				contentMap.put(tc.getId(), tc);
				sb.append(tc.getId()+",");
			}
			if(sb.length() > 0){
				fatherIds = sb.substring(0, sb.length()-1);
				String subSql = "SELECT user_t.name AS to_user_name,user_t.id AS to_user_id,user_t.user_picture AS to_user_picture," +
						"user_f.name AS from_user_name,user_f.id AS from_user_id,user_f.user_picture AS from_user_picture,tmp.* " +
						"FROM v_user user_t FORCE INDEX(PRIMARY) " +
						"RIGHT JOIN (SELECT content.id,content.content,content.topic_id,content.create_time ,content.father_id,content.from_user_id,content.to_user_id  " +
						"FROM v_topic_content content FORCE INDEX(i_id) WHERE content.father_id IN ("+fatherIds+") ORDER BY content.create_time DESC) AS tmp " +
								"ON tmp.to_user_id=user_t.id LEFT JOIN v_user USER_f FORCE INDEX(PRIMARY) ON tmp.from_user_id=user_f.id  ";
				//获取所有的评论的回复
				List<TopicContent> subContents = jdbcTemplate.query(subSql, new RowMapper<TopicContent>(){  
					@Override
					public TopicContent mapRow(java.sql.ResultSet rs, int arg1)
							throws SQLException {
						TopicContent content = new TopicContent();  
		                try {  
		                	content.setId(rs.getLong("id"));  
		                	content.setContent(rs.getString("content"));
		                	content.setTopicId(rs.getLong("topic_id"));
		                	content.setCreateTime(rs.getInt("create_time"));
		                	content.setFatherId(rs.getLong("father_id"));
		                	User fromUser = new User();
		                	
		                	fromUser.setName(rs.getString("from_user_name"));
		                	fromUser.setId(rs.getLong("from_user_id"));
		                	fromUser.setUserPicture(rs.getString("from_user_picture"));
		                	content.setFromUser(fromUser);
		                	
		                	User toUser = new User();
		                	toUser.setName(rs.getString("to_user_name"));
		                	toUser.setId(rs.getLong("to_user_id"));
		                	toUser.setUserPicture(rs.getString("to_user_picture"));
		                	content.setToUser(toUser);
		                } catch (SQLException e) {  
		                }  
		                return content;
					}  
		        });
				//封装评论对象和评论的回复
				for (int i=subContents.size()-1;i>=0;i--) {
					TopicContent subC = subContents.get(i);
					TopicContent tmpC = contentMap.get(subC.getFatherId());
					List<TopicContent> subContentList = tmpC.getSubContentList();
					if(subContentList == null){
						subContentList = new ArrayList<TopicContent>();
						subContentList.add(subC);//@TODO 测试基于地址传递
						tmpC.setSubContentList(subContentList);
						contentMap.put(subC.getFatherId(), tmpC);//测试是否需要put
					}else{
						subContentList.add(subC);
					}
				}
				for(int i=0;i<contents.size();i++){
					TopicContent tc = contents.get(i);
					result.add(i,contentMap.get(tc.getId()));
				}
			}
		}
		return result;
	}
	
	/**
	 * 保存用户赞的回复贴，若已经赞过，则该操作为取消赞
	 * @param contentId 回复帖id
	 * @param userId 操作的用户id
	 *	@return 1：赞成功，2：已经赞，0：操作失败
	 */
	public int saveTopicContentGreat(long contentId,long userId){
		String sql = "";
		int type = 0;
		long time = System.currentTimeMillis();
		String querySql = "select record_id from v_topic_great_user where user_id = ? and record_id = ? and type=2";
		long recordId = 0;
		try {
			recordId = jdbcTemplate.queryForLong(querySql,new Object[]{userId,contentId});
		} catch (Exception e) {
			//没有赞的记录，进行赞的操作
		}
		
		
		try {
			if(recordId == 0){//增加赞的次数
				sql = "update v_topic_content set great_times=great_times+1 where id=?";
				String resultSql = "insert into v_topic_great_user(record_id,user_id,time,type) values(?,?,?,?)";
				int update = jdbcTemplate.update(resultSql, new Object[]{contentId,userId,time/1000,2});
				int updateRecord = jdbcTemplate.update(sql, new Object[]{contentId});
				if(update > 0 && updateRecord >0){
					type = 1;
				}
			}
			if(recordId > 0){
				//已经赞过，提示已经赞
				type = 2;
			}
		} catch (Exception e) {
			type = 0;
			e.printStackTrace();
		}
		return type;
	}
	
	/**
	 * 保存用户赞的主题帖，若已经赞过，则为取消赞
	 * @param topicId 回复帖id
	 * @param userId 用户id
	 * @return 1：赞成功，2：已经赞，0：操作失败
	 */
	public int saveTopicGreatCount(long topicId,long userId){
		String querySql = "select record_id from v_topic_great_user where user_id = ? and record_id = ? and type=1";
		long recordId = 0;
		try {
			recordId = jdbcTemplate.queryForLong(querySql,new Object[]{userId,topicId});
		} catch (Exception e) {
		}
		String sql = "";
		int type = 0;
		long time = System.currentTimeMillis();
		try {
			if(recordId == 0){//增加赞的次数
				sql = "update v_topic set topic_great_count=topic_great_count+1 where id=?";
				String resultSql = "insert into v_topic_great_user(record_id,user_id,time,type) values(?,?,?,?)";
				int updateRecord = jdbcTemplate.update(resultSql, new Object[]{topicId,userId,time/1000,1});
				int update = jdbcTemplate.update(sql, new Object[]{topicId});
				if(update >0 && updateRecord > 0){
					type = 1;
				}
			}
			if(recordId > 0){//取消赞
//			sql = "update v_topic set topic_great_count=topic_great_count-1 where id=?";
//			String resultSql = "delete from v_topic_great_user where user_id=? and record_id=? and type=1";
//			jdbcTemplate.update(resultSql, new Object[]{userId,topicId});
				type = 2;
			}
		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
		return type;
		
	}
	/**
	 * 根据分类categoryNumber获取所有的相关主题帖
	 * @param categoryNumber 分类number
	 * @param type 商品类型，1为标准商品，2为本地生活服务，3为二手商品
	 * @param page 页码
	 * @param pageSize 每页大小
	 * @param order 排序方式:1,以主题帖的热度,2发布时间倒序,3销量,4评分,5价格,默认为主题帖发布时间
	 * @return List<Topic>
	 */
	public List<Topic> getTopicListByCategoryNumber(int categoryNumber,int page,int pageSize,int order,int type){
		String orderString = "topic.topic_create_time";
		List<Topic> topics;
		String subSql = null;
		Object[] param = null ;
		String number = ""+type+categoryNumber;
		int startKey  = Integer.parseInt(number);
		int stopKey = 0;//处理后的between ?,?
		int i = number.length()-1;
		while(i>=0){
			char tmp = number.charAt(i);
			char c1 = '0';
			if(c1 !=tmp){
				String afterNumber = number.substring(0, i+1);
				int num = Integer.parseInt(afterNumber);
				String finalS = (num+1)+""+number.substring(i+1, number.length());
				stopKey = Integer.parseInt(finalS);
				break;
			}
			i--;
		}
		if(order == 1){
			//热度倒序
			subSql = "SELECT product.id AS pid,product.product_name,product.product_market_price,product.product_description,product.product_url,product.product_status,product.product_type,product.product_category_number,product.product_brand,product.product_main_picture,product.uid,product.product_create_time,tmp.* " +
					"FROM (SELECT topic.topic_hot,topic.category_number,topic.id AS tid,topic.topic_title,topic.topic_string,topic.uid,topic.topic_status,topic.topic_browse_count,topic.topic_content_count,topic.topic_user_count,topic.product_id,topic.topic_user_name,topic.topic_create_time  " +
					"FROM v_topic topic WHERE topic.product_type=? and topic.category_number>=? and topic.category_number<?  ORDER BY topic.topic_hot DESC LIMIT ?,?) AS tmp LEFT JOIN v_product product ON product.id=tmp.product_id"; 
			param = new Object[]{type,startKey,stopKey,(page * pageSize - pageSize), pageSize+1};
		}
		if(order == 2){
			//时间倒序
			subSql = "SELECT product.id AS pid,product.product_name,product.product_market_price,product.product_description,product.product_url,product.product_status,product.product_type,product.product_category_number,product.product_brand,product.product_main_picture,product.uid,product.product_create_time,tmp.* " +
					"FROM (SELECT topic.topic_hot,topic.category_number,topic.id AS tid,topic.topic_title,topic.topic_string,topic.uid,topic.topic_status,topic.topic_browse_count,topic.topic_content_count,topic.topic_user_count,topic.product_id,topic.topic_user_name,topic.topic_create_time  " +
					"FROM v_topic topic WHERE topic.product_type=? and topic.category_number >= ? and topic.category_number<?  ORDER BY topic.topic_create_time DESC LIMIT ?,?) AS tmp LEFT JOIN v_product product ON product.id=tmp.product_id"; 
			param = new Object[]{type,startKey,stopKey,(page * pageSize - pageSize), pageSize+1};
		}
		final String sql = subSql;
		topics = simpleJdbcTemplate.query(sql,new RowMapper<Topic>() {
			@Override
			public Topic mapRow(ResultSet rs, int row) throws SQLException {
				Topic t = new Topic();  
				try {  
					t.setId(rs.getLong("tid"));  
					t.setTopicTitle(rs.getString("topic_title"));
					t.setTopicString(rs.getString("topic_string"));
					t.setUid(rs.getLong("uid"));
					t.setTopicHot(rs.getInt("topic_hot"));
					t.setCategoryNumber(rs.getInt("category_number"));
					t.setProductType(rs.getInt("product_type"));
					t.setTopicStatus(rs.getInt("topic_status"));
					t.setTopicBrowseCount(rs.getInt("topic_browse_count"));
					t.setTopicContentCount(rs.getInt("topic_content_count"));
					t.setTopicUserCount(rs.getInt("topic_user_count"));
					t.setProductId(rs.getLong("product_id"));
					t.setTopicUserName(rs.getString("topic_user_name"));
					t.setTopicCreateTime(rs.getLong("topic_create_time")*1000);
					Product p = new Product();
					p.setId(rs.getLong("pid"));
					p.setProductName(rs.getString("product_name"));
					p.setProductMarketPrice(rs.getFloat("product_market_price"));
					p.setProductDescription(rs.getString("product_description"));
					p.setProductUrl(rs.getString("product_url"));
					p.setProductStatus(rs.getInt("product_status"));
					p.setProductType(rs.getInt("product_type"));
					p.setProductCategoryNumber(rs.getInt("product_category_number"));
					p.setProductBrand(rs.getString("product_brand"));
					p.setProductMainPicture(rs.getString("product_main_picture"));
					p.setUid(rs.getLong("uid"));
					p.setProductCreateTime(rs.getLong("product_create_time")*1000);
					t.setProduct(p);
				} catch (SQLException e) {  
				}  
				return t;
			}
		},param );
		return topics;
	}

}
